MySQL: Table design and queries Database Management System - Java, Java Swing, OOAD, MIS, DSA

Data Base

MySQL: Table design and queries

Creating table named department; departid is primary key of type integer and departname is string of size 30. The possible values for department name are finance, store, marketing, humanresource

create table department(
    departid int primary key AUTO_INCREMENT,
    departname varchar(30) check (departname in ('finance','store','marketing','humanresource')));

Inserting department information in department table. To insert more than one record, we separate each record with comma and each record is enclosed in parenthesis.

insert into department(departname) values('finance'),('humanresource'),('marketing');

Inserting departments in department table. We can not insert department with name hello as it violates check constraint specified for depertname.

insert into department(departname) values('hello');

This will be inserted.

insert into department(departname) values('store');

To retrieve or display records we use select statement.

SELECT * FROM department;

Retrieving record of male employees or employees who have joined deparment after 2025-05-01.

SELECT * FROM `employees` WHERE gender = 'Male' or joined_date > '2025-05-01'

Creating employees table, it has eid as primary key, did as foreign key,ename, gender,joined_date. The possible values for gender are male,female, and other. Did attribute is a foreign key which references departid of department table.

create table employees(
    eid int primary key AUTO_INCREMENT,
    did int, 
    ename varchar(30),
    joined_date date,
    gender varchar(10) check (gender in ('Male','Female','Other')),
    constraint fk_employees_1 FOREIGN key(did) REFERENCES department(departid)
    )

Inserting employee information to employees table. We can not insert following employee record as there is no department with id 2 so, insert fails, foreign key violation.

insert into employees(did,ename,joined_date,gender) values(2,'Ramu','2025-09-10','Male');

Following record will be inserted.

insert into employees(did,ename,joined_date,gender) values(5,'Ramu','2025-09-10','Male');

Retrieving records of all employees:

select * from employees;

Retrieving records of male employees only, We use where clause to specify condition in select statement.

select * from employees where gender = 'Male';

Inserting more than one record at a time

insert into employees(did,ename,joined_date,gender) values(1,'Sitesh','2025-04-05','Male'),(6,'Meera','2025-01-01','Female'),(6,'Geeta','2025-03-01','Female'),(7,'Gopi','2025-01-01','Male')

Displaying records of female employees working in department id 6. Here and operator is used to specify more than one condition, all the specified conditions must be true, then only records will be retrived. If condition is not satisfied then null or empty table (relation) will be returned.

SELECT ename FROM `employees` WHERE gender='Female' and did = 6;

Displaying name and joined_date of employees working in marketing department.

Here, we do not have department name attribute in employees table, so, we join employees table with department table. These two tables have department id as a common attribute . We have made did in employees table as foreign key referencing departid of department table.

Generally, we us common attributes of tables to join tables. Here, we have equated e.did with d.departid; specified name of department using and operator. Department name is stored in department table.

SELECT ename,joined_date FROM `employees` as e,department as d WHERE e.did = d.departid and departname='marketing' drop table employees;

Online-Academy

Look, Read, Understand, Apply

Data Base